package com.lms.dao.user;

import com.mysql.cj.util.StringUtils;
import com.lms.dao.BaseDao;
import com.lms.bean.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao{
    private PreparedStatement pstm=null;
    private ResultSet rs=null;
    @Override
    public User getLoginUser(Connection connection, String userCode) {
        pstm=null;
        rs=null;
        User user=null;

        if (connection!=null){
            try {
                String sql="select * from user_account where userCode=?;";
                Object[] params={userCode};
                rs= BaseDao.execute(connection,pstm,rs,sql,params);

                if (rs.next()){
                    user=new User();
                    user.setUserCode(rs.getString("userCode"));
                    user.setUserName(rs.getString("userName"));
                    user.setUserPassWord(rs.getString("userPassword"));
                    user.setUserRole(rs.getInt("userRole"));
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }finally {
                BaseDao.closeResource(connection,pstm,rs);
            }
        }

        return user;
    }

    @Override
    public int updatePwd(Connection connection, String id, String pwd) throws SQLException {
        int execute=0;
        if (connection!=null){
            String sql="update user_account set userPassword=? where userCode=?";
            PreparedStatement preparedStatement=null;
            Object[] parama={pwd,id};
            execute = BaseDao.execute(connection,preparedStatement,sql,parama);
            BaseDao.closeResource(connection,preparedStatement,null);
        }
        return execute;
    }

    @Override
    public int getUserCount(Connection connection, String userName, int userRole) throws Exception {
        pstm=null;
        rs=null;
        int count=0;

        if (connection!=null){
            StringBuffer sql=new StringBuffer("select count(1) as count " +
                    "from smbms_user u,smbms_role r where u.userRole=r.id");
            List<Object> list=new ArrayList<>();
            if (!StringUtils.isNullOrEmpty(userName)){
                sql.append(" and u.username like ?");
                list.add("%"+userName+"%");
            }
            if (userRole>0){
                sql.append(" and r.id=?");
                list.add(userRole);
            }
            Object[] params= list.toArray();

            rs=BaseDao.execute(connection,pstm,rs,sql.toString(),params);

            if (rs.next()){
                count=rs.getInt("count");
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return count;
    }

    @Override
    public List<User> getUserList(Connection connection) throws Exception {
        List<User> userList=new ArrayList<>();

        if (connection!=null){
            StringBuffer sql=new StringBuffer("select * from user_account");
            Object[] params = {};
            rs = BaseDao.execute(connection, pstm, rs, sql.toString(), params);
            while (rs.next()){
                User user = new User();
                user.setUserCode(rs.getString("userCode"));
                user.setUserName(rs.getString("userName"));
                user.setUserPassWord(rs.getString("userPassWord"));
                user.setUserRole(rs.getInt("userRole"));
                userList.add(user);
            }
            BaseDao.closeResource(connection,pstm,rs);
        }
        return userList;
    }

    @Override
    public boolean addUser(Connection connection, User newUser) throws Exception {
        int n=0;
        if (connection!=null){
            String sql="insert into smbms_user(userCode, userName, userPassword, " +
                    "gender, birthday, phone, address, userRole, createdBy, creationDate) " +
                    "values(?,?,?,?,?,?,?,?,?,?);";
//            Object[] params={newUser.getUserCode(),newUser.getUserName(),newUser.getUserPassWord(),
//                    newUser.getGender(),newUser.getBirthday(),newUser.getPhone(),newUser.getAddress(),newUser.getUserRole(),
//                    newUser.getCreatedBy(),newUser.getCreationDate()};
//            n=BaseDao.execute(connection,pstm,sql,params);
            BaseDao.closeResource(null,pstm,rs);//此处不能关闭Connection会导致上层函数调用异常
        }
        if (n>0){
            return true;
        }else {
            return false;
        }
    }

    @Override
    public int modifyById(Connection connection, User user) throws SQLException {
        int row=0;
//        if (connection!=null){
//            String sql="update smbms_user set " +
//                    "userName=?,gender=?,birthday=?,phone=?,address=?,userRole=?,modifyBy=?,modifyDate=? where id=?";
//            Object[] params={user.getUserName(),user.getGender(),user.getBirthday(),user.getPhone()
//                    ,user.getAddress(),user.getUserRole(),user.getModifyBy(),user.getModifyDate(),user.getId()};
//            row = BaseDao.execute(connection, pstm, sql, params);
//            BaseDao.closeResource(null,pstm,null);
//        }
        return row;
    }

    @Override
    public String getRootMsg(Connection connection) throws SQLException {
        String rootMessage="null";
        if (connection!=null){
            String sql="select rootMessage from root_account where rootGrade=1";
            rs = BaseDao.execute(connection, pstm, this.rs, sql, new Object[0]);
            while (rs.next()){
                rootMessage = rs.getString("rootMessage");
            }
            BaseDao.closeResource(null,pstm,rs);
        }
        return rootMessage;
    }
}



